﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using Pixysoft.Framework.Verifications;

namespace Pixysoft.Framework.Noebe.Orm.Controller
{
    static class OrmCommandController
    {
        /// <summary>
        /// 根据表明获取表结构
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataTable GetSchema(string tableName)
        {
            DataTable table = NoebeCommandHelper.GetNoebeManager().GetEntity(tableName);

            if (table == null)
                throw Exceptions.VerificationFailedException(table);

            return table;
        }


        /// <summary>
        /// 根据对象的sid和名字，获取对象pojo数据
        /// </summary>
        /// <param name="command"></param>
        /// <param name="objName"></param>
        /// <param name="fieldName"></param>
        /// <param name="sidValue"></param>
        /// <returns></returns>
        public static DataTable GetObjectTable(INoebeCommand command, string objName, Int64 sidValue)
        {
            string tbName = MappingHelper.GetTableName(objName);

            string sidName = MappingHelper.GetSysIDName();

            string paraName = MappingHelper.GetParamName(tbName, sidName);

            string sql = string.Format("SELECT * FROM {0} WHERE {1} = :{2}", tbName, sidName, paraName);

            command.SQL = sql;

            command.Parameters.Add(paraName, sidValue);

            DataTable table = command.ExecuteReader();

            if (table.Rows.Count == 0)
                return table;

            if (table.Rows.Count > 1)
                throw Exceptions.UnexpectedResultException("multi data exist for {0}.{1}", objName, sidValue);


            //09-10-30 显式标注表名

            table.TableName = tbName;

            return table;
        }
        /// <summary>
        /// 删除对象
        /// </summary>
        /// <returns></returns>
        public static int DeleteObject(INoebeCommand command, string objName, Int64 sidValue)
        {
            string tbname = MappingHelper.GetTableName(objName);

            string sidName = MappingHelper.GetSysIDName();

            DataTable table = OrmCommandController.GetSchema(tbname);

            DataRow row = table.NewRow();

            row[sidName] = sidValue;

            table.Rows.Add(row);

            command.CascadeDelete(table);

            return 1;
        }


        /// <summary>
        /// 创建值类型链接表
        /// </summary>
        /// <returns></returns>
        public static DataTable CreateLinkValueTable(string objectName, string fieldName, Int64 sidValue, IList values, string groupCode)
        {
            string linkTbName = MappingHelper.GetLinkTableName(objectName, fieldName);

            string linkValueName = MappingHelper.GetLinkValueName(fieldName);

            string linkFmName = MappingHelper.GetLinkFromName(objectName);

            string linkPosName = MappingHelper.GetListPosName(fieldName);

            string sidName = MappingHelper.GetLinkTbSysIdName(linkTbName);

            DataTable linkValueTable = OrmCommandController.GetSchema(linkTbName);


            //使用时间序列作为位置排序索引

            Int64 positionIndex = StringHelper.CreatePositionIndex();

            Int64 pkCounter = StringHelper.CreatePrimaryKey(values.Count);

            foreach (object tValue in values)
            {
                DataRow linkValueRow = linkValueTable.NewRow();

                linkValueRow[sidName] = pkCounter++;

                linkValueRow[linkFmName] = sidValue;

                if (tValue == null)
                    linkValueRow[linkValueName] = DBNull.Value;
                else
                    linkValueRow[linkValueName] = tValue;

                linkValueRow[linkPosName] = positionIndex++;

                linkValueRow[StringHelper.ORM_CREATEDATE] = Pixysoft.Tools.GlobalTimer.Instance.GetGlobalTime();

                linkValueRow[StringHelper.ORM_MODIDATE] = Pixysoft.Tools.GlobalTimer.Instance.GetGlobalTime();

                linkValueRow[StringHelper.ORM_GROUPCODE] = groupCode;

                linkValueTable.Rows.Add(linkValueRow);
            }

            //09-10-30 显式标注表名

            linkValueTable.TableName = linkTbName;

            return linkValueTable;

        }
        /// <summary>
        /// 获取值链接表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetLinkValueTable(INoebeCommand command, string objName, string fieldName, Int64 sidValue)
        {
            string linkTbName = MappingHelper.GetLinkTableName(objName, fieldName);

            string linkFmName = MappingHelper.GetLinkFromName(objName);

            string linkFmParaName = MappingHelper.GetParamName(linkTbName, linkFmName);

            string linkToName = MappingHelper.GetLinkToName(fieldName);

            string PosName = MappingHelper.GetListPosName(fieldName);


            string sql = string.Format("SELECT * FROM {1} WHERE {2} = :{3} ORDER BY {4}",
                linkToName, linkTbName, linkFmName, linkFmParaName, PosName);

            command.SQL = sql;

            command.Parameters.Add(linkFmParaName, sidValue);

            DataTable table = command.ExecuteReader();

            //09-10-30 显式标注表名

            table.TableName = linkTbName;

            return table;
        }
        /// <summary>
        /// 移除值类型集合
        /// </summary>
        /// <returns></returns>
        public static int RemoveFromValueList(INoebeCommand command, string objectName, string fieldName, Int64 sidValue, IList values)
        {
            //DELETE FROM LINKTB WHERE LINKFMNAME = :LINKFMNAME AND LINKVALUENAME = :LINKVALUENAME

            string linkTbName = MappingHelper.GetLinkTableName(objectName, fieldName);

            string linkValueName = MappingHelper.GetLinkValueName(fieldName);

            string linkFmName = MappingHelper.GetLinkFromName(objectName);

            string linkFmParaName = MappingHelper.GetParamName(linkTbName, linkFmName);

            string linkValueParaName = MappingHelper.GetParamName(linkTbName, linkValueName);

            string sql = string.Format("DELETE FROM {0} WHERE {1} = :{2} AND {3} = :{4}",
                linkTbName, linkFmName, linkFmParaName, linkValueName, linkValueParaName);

            command.SQL = sql;

            int counter = 0;

            foreach (object value in values)
            {
                command.Parameters.DeepClear();

                command.Parameters.Add(linkFmParaName, sidValue);

                command.Parameters.Add(linkValueParaName, value);

                counter += command.Execute();
            }

            return counter;
        }
        /// <summary>
        /// 删除链接表
        /// </summary>
        /// <param name="command"></param>
        /// <param name="objName"></param>
        /// <param name="fieldName"></param>
        /// <param name="sidValue"></param>
        public static int DeleteValueList(INoebeCommand command, string objName, string fieldName, Int64 sidValue)
        {
            return DeleteList(command, objName, fieldName, sidValue);
        }


        /// <summary>
        /// 添加数据到链接表
        /// </summary>
        /// <returns></returns>
        public static DataTable CreateLinkToTable(string objectName, string fieldName, Int64 sidValue, List<Int64> tSidValues, string groupCode)
        {
            string linkTbName = MappingHelper.GetLinkTableName(objectName, fieldName);

            string linkToName = MappingHelper.GetLinkToName(fieldName);

            string linkFmName = MappingHelper.GetLinkFromName(objectName);

            string linkPosName = MappingHelper.GetListPosName(fieldName);

            string sidName = MappingHelper.GetLinkTbSysIdName(linkTbName);


            DataTable table = OrmCommandController.GetSchema(linkTbName);

            Int64 positionIndex = StringHelper.CreatePositionIndex();

            Int64 primaryKeyCount = StringHelper.CreatePrimaryKey(tSidValues.Count);

            foreach (Int64 tsidValue in tSidValues)
            {
                DataRow row = table.NewRow();

                row[sidName] = primaryKeyCount++;

                row[linkFmName] = sidValue;

                if (tsidValue == StringHelper.GetNullPrimaryKey())
                    row[linkToName] = DBNull.Value;
                else
                    row[linkToName] = tsidValue;

                row[linkPosName] = positionIndex++;

                row[StringHelper.ORM_CREATEDATE] = Pixysoft.Tools.GlobalTimer.Instance.GetGlobalTime();

                row[StringHelper.ORM_MODIDATE] = Pixysoft.Tools.GlobalTimer.Instance.GetGlobalTime();

                row[StringHelper.ORM_GROUPCODE] = groupCode;

                table.Rows.Add(row);
            }

            //09-10-30 显式标注表名

            table.TableName = linkTbName;

            return table;
        }
        /// <summary>
        /// 取得目标表合并结构
        /// </summary>
        /// <returns></returns>
        public static DataTable GetLinkToTable(INoebeCommand command, string objName, string fieldName, Int64 sidValue, string tObjName)
        {
            string tTableName = MappingHelper.GetTableName(tObjName);

            string tTableMaping = tTableName + "0";

            string tSidName = MappingHelper.GetSysIDName();


            string linkTbName = MappingHelper.GetLinkTableName(objName, fieldName);

            string linkTbMapping = linkTbName + "1";


            string linkFmName = MappingHelper.GetLinkFromName(objName);

            string linkFmParaName = MappingHelper.GetParamName(linkTbName, linkFmName);

            string linkToName = MappingHelper.GetLinkToName(fieldName);

            string PosName = MappingHelper.GetListPosName(fieldName);

            //          0            0                 1            0     2        1       3           1         4          5                1       6
            //select ttable.* from ttable inner join linktable on ttable.sid = linktable.tosid where linktable.fromsid = :fromsid order by linktable.pos
            string sql = string.Format("SELECT {7}.* FROM {0} AS {7} INNER JOIN {1} AS {8} ON {7}.{2} = {8}.{3} WHERE {8}.{4} = :{5} ORDER BY {8}.{6}",
             tTableName, linkTbName, tSidName, linkToName, linkFmName, linkFmParaName, PosName, tTableMaping, linkTbMapping);

            command.SQL = sql;

            command.Parameters.Add(linkFmParaName, sidValue);

            DataTable table = command.ExecuteReader();

            table.TableName = tTableName;

            return table;
        }
        /// <summary>
        /// 删除linktable里面的数据
        ///
        public static int RemoveFromList(INoebeCommand command, string objectName, string fieldName, Int64 sidValue, List<Int64> tSidValues)
        {
            //DELETE FROM LINKTB WHERE LINKFMNAME = :LINKFMNAME AND LINKTONAME = :LINKTOVALUE

            string linkTbName = MappingHelper.GetLinkTableName(objectName, fieldName);

            string linkToName = MappingHelper.GetLinkToName(fieldName);

            string linkFmName = MappingHelper.GetLinkFromName(objectName);

            string linkFmParaName = MappingHelper.GetParamName(linkTbName, linkFmName);

            string linkToParaName = MappingHelper.GetParamName(linkTbName, linkToName);

            string sql = string.Format("DELETE FROM {0} WHERE {1} = :{2} AND {3} = :{4}",
                linkTbName, linkFmName, linkFmParaName, linkToName, linkToParaName);

            command.SQL = sql;

            int counter = 0;

            foreach (Int64 tsid in tSidValues)
            {
                if (tsid == StringHelper.GetNullPrimaryKey())
                    continue;

                command.Parameters.DeepClear();

                command.Parameters.Add(linkFmParaName, sidValue);

                command.Parameters.Add(linkToParaName, tsid);

                counter += command.Execute();
            }

            return counter;
        }
        /// <summary>
        /// 删除链接表
        /// </summary>
        /// <param name="objName"></param>
        /// <param name="fieldName"></param>
        /// <param name="sidValue"></param>
        public static int DeleteList(INoebeCommand command, string objName, string fieldName, Int64 sidValue)
        {
            string linkTbName = MappingHelper.GetLinkTableName(objName, fieldName);

            string linkToName = MappingHelper.GetLinkToName(fieldName);

            string linkFmName = MappingHelper.GetLinkFromName(objName);

            string linkFmParaName = MappingHelper.GetParamName(linkTbName, linkFmName);

            //DELETE FROM LINKTB WHERE LINKFMNAME = :LINKFMNAME

            string sql = string.Format("DELETE FROM {0} WHERE {1} = :{2}", linkTbName, linkFmName, linkFmParaName);

            command.SQL = sql;

            command.Parameters.Add(linkFmParaName, sidValue);

            return command.Execute();
        }


        /// <summary>
        /// 取得用户自定义主键查询
        /// </summary>
        /// <param name="objName"></param>
        /// <param name="fieldName"></param>
        /// <param name="tObjName"></param>
        /// <param name="upkName"></param>
        /// <param name="sidValue"></param>
        /// <param name="upk"></param>
        /// <returns></returns>
        public static DataTable GetSelectTable(INoebeCommand command, string objName, string fieldName, Int64 sidValue,
            string tObjName, string upkName, object upk)
        {
            //        0     1          0                 2            2       3      0    1         0       4    5        2       6      7
            //SELECT TABLE2.SID FROM TABLE2 INNER JOIN LINKTABLE ON LINKTABLE.TO = TABLE2.SID WHERE TABLE2.UPK=:UPK AND LINKTABLE.FM  = :FM

            //09-10-29 

            //SELECT TABLE2.* FROM TABLE2 INNER JOIN LINKTABLE ON LINKTABLE.TO = TABLE2.SID WHERE TABLE2.UPK=:UPK AND LINKTABLE.FM  = :FM

            int counter = 0;

            string linkTbName = MappingHelper.GetLinkTableName(objName, fieldName);

            string linkTbMapping = linkTbName + counter++;

            string linkFmName = MappingHelper.GetLinkFromName(objName);

            string linkFmParaName = MappingHelper.GetParamName(linkTbName, linkFmName);

            string linkToName = MappingHelper.GetLinkToName(fieldName);


            string tTbName = MappingHelper.GetTableName(tObjName);

            string tTbMapping = tTbName + counter++;

            string tSidName = MappingHelper.GetSysIDName();

            string upkParaName = MappingHelper.GetParamName(tTbName, upkName);



            string sql = string.Format("SELECT {8}.* FROM {0} AS {8} INNER JOIN {2} AS {9} ON {9}.{3} = {8}.{1} WHERE {8}.{4} = :{5} AND {9}.{6} = :{7}",
                tTbName, tSidName, linkTbName, linkToName, upkName, upkParaName, linkFmName, linkFmParaName, linkTbMapping, tTbMapping);

            command.SQL = sql;

            command.Parameters.Add(linkFmParaName, sidValue);

            command.Parameters.Add(upkParaName, upk);

            DataTable table = command.ExecuteReader();

            //09-10-30 显式标注表名

            //09-12-13 bug 是对象table，不是linktable

            table.TableName = tTbName;

            //table.TableName = linkTbName;

            return table;
        }


    }
}
